﻿@using DevExtreme.NETCore.Demos.Models
@model IEnumerable<Order>

@section ExternalDependencies {
    <script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/3.3.1/exceljs.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/7.4.0/polyfill.min.js"></script>
}

<div id="header">Sales amounts report</div>
@(Html.DevExtreme().DataGrid<Order>()
    .ID("gridContainer")
    .DataSource(Model)
    .ShowBorders(true)
    .GroupPanel(groupPanel => groupPanel.Visible(true))
    .Grouping(grouping => grouping.AutoExpandAll(true))
    .SortByGroupSummaryInfo(i => i.Add().SummaryItem("count"))
    .OnCellPrepared("cellPrepared")
    .Selection(s => s.Mode(SelectionMode.Multiple))
    .Export(e => e.Enabled(true).AllowExportSelectedData(true))
    .OnExporting("exporting")
    .Columns(columns =>
    {
        columns.AddFor(m => m.Employee)
            .GroupIndex(0);

        columns.AddFor(m => m.OrderNumber)
            .Width(130);

        columns.AddFor(m => m.OrderDate)
            .Width(160);

        columns.AddFor(m => m.CustomerStoreCity)
            .GroupIndex(1);

        columns.AddFor(m => m.CustomerStoreState)
            .CellTemplate(@<text>
                <a href="http://example.com" target="_blank">
                    <%- value %>
                </a>
            </text>); ;

        columns.AddFor(m => m.SaleAmount)
            .Alignment(HorizontalAlignment.Right)
            .Format(Format.Currency)
            .SortOrder(SortOrder.Desc);
        })
        .Summary(s => s
            .GroupItems(groupItems =>
            {
                groupItems.AddFor(m => m.OrderNumber)
                    .SummaryType(SummaryType.Count)
                    .DisplayFormat("{0} orders")
                    .AlignByColumn(false);

                groupItems.AddFor(m => m.SaleAmount)
                    .SummaryType(SummaryType.Max)
                    .DisplayFormat("Max: {0}")
                    .ValueFormat(Format.Currency)
                    .AlignByColumn(true)
                    .ShowInGroupFooter(false);

                groupItems.AddFor(m => m.SaleAmount)
                    .SummaryType(SummaryType.Sum)
                    .DisplayFormat("Sum: {0}")
                    .ValueFormat(Format.Currency)
                    .AlignByColumn(true)
                    .ShowInGroupFooter(true);
            })
            .TotalItems(totalItems =>
            {
                totalItems.AddFor(m => m.SaleAmount)
                    .SummaryType(SummaryType.Sum)
                    .DisplayFormat("Total Sum: {0}")
                    .ValueFormat(Format.Currency);
            })
        )
)
<div id="footer">For demonstration purposes only</div>

<script>
    function exporting(e) {
        var workbook = new ExcelJS.Workbook();
        var worksheet = workbook.addWorksheet('Main sheet');

        /*
            The 'DevExpress.excelExporter.exportDataGrid' function uses the ExcelJS library.
            For more information about ExcelJS, see:
            - https://github.com/exceljs/exceljs#contents
            - https://github.com/exceljs/exceljs#browser
        */

        DevExpress.excelExporter.exportDataGrid({
            component: e.component,
            worksheet: worksheet,
            topLeftCell: { row: 4, column: 1 },
            customizeCell: function (options) {

                /*
                    The 'options.excelCell' field contains an ExcelJS object that describes an Excel cell.
                    Refer to the following topics for more details about its members:
                    - value and type - https://github.com/exceljs/exceljs#value-types
                    - alignment - https://github.com/exceljs/exceljs#alignment
                    - border - https://github.com/exceljs/exceljs#borders
                    - fill - https://github.com/exceljs/exceljs#fills
                    - font - https://github.com/exceljs/exceljs#fonts
                    - numFmt - https://github.com/exceljs/exceljs#number-formats
                    The 'options.gridCell' object fields are described in https://js.devexpress.com/Documentation/ApiReference/Common/Object_Structures/ExcelDataGridCell/
                */

                var gridCell = options.gridCell;
                var excelCell = options.excelCell;
                if (gridCell.rowType === 'data') {
                    if (gridCell.data.OrderDate < new Date(2014, 2, 3)) {
                        excelCell.font = { color: { argb: 'AAAAAA' } };
                    }
                    if (gridCell.data.SaleAmount > 15000) {
                        if (gridCell.column.dataField === 'SaleAmount') {
                            Object.assign(excelCell, {
                                font: { color: { argb: '000000' } },
                                fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFBB00' } }
                            });
                        }
                    }
                    if (gridCell.column.dataField === 'CustomerStoreState') {
                        Object.assign(excelCell, {
                            value: { text: gridCell.value, hyperlink: 'http://example.com' },
                            font: { color: { argb: 'FF0000FF' }, underline: true }
                        });
                    }
                }
                if (gridCell.rowType === 'group') {
                    var nodeColors = ['BEDFE6', 'C9ECD7'];
                    Object.assign(excelCell, {
                        fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: nodeColors[gridCell.groupIndex] } }
                    });
                }
                if (gridCell.rowType === 'groupFooter' && excelCell.value) {
                    Object.assign(excelCell.font, { italic: true });
                }
            }
        }).then(function (dataGridRange) {
            // header
            worksheet.getRow(2).height = 20; // https://github.com/exceljs/exceljs#rows
            worksheet.mergeCells(2, 1, 2, 4); // https://github.com/exceljs/exceljs#merged-cells
            Object.assign(worksheet.getRow(2).getCell(1), {
                value: 'Sales amounts report',
                font: { bold: true, size: 16 },
                alignment: { horizontal: 'center' }
            });
            // footer
            var currentRowIndex = dataGridRange.to.row + 2;
            worksheet.mergeCells(currentRowIndex, 1, currentRowIndex, 4);
            worksheet.getRow(currentRowIndex).getCell(1).value = 'For demonstration purposes only';
            Object.assign(worksheet.getRow(currentRowIndex).getCell(1), {
                font: { italic: true },
                alignment: { horizontal: 'right' }
            });
            return Promise.resolve();
        }).then(function () {
            workbook.xlsx.writeBuffer().then(function (buffer) { // https://github.com/exceljs/exceljs#writing-xlsx
                saveAs(new Blob([buffer], { type: 'application/octet-stream' }), 'DataGrid.xlsx');
            });
        });
        e.cancel = true;
    }

    function cellPrepared(e) {
        if (e.rowType === 'data') {
            if (e.data.OrderDate < new Date(2014, 2, 3)) {
                e.cellElement.css({ color: '#AAAAAA' });
            }
            if (e.data.SaleAmount > 15000) {
                if (e.column.dataField === 'OrderNumber') {
                    e.cellElement.css({ 'font-weight': 'bold' });
                }
                if (e.column.dataField === 'SaleAmount') {
                    e.cellElement.css({ 'background-color': '#FFBB00', 'color': '#000' });
                }
            }
        }
        if (e.rowType === 'group') {
            var nodeColors = ['#BEDFE6', '#C9ECD7'];
            e.cellElement.css({ 'background-color': nodeColors[e.row.groupIndex], 'color': '#000' });
            e.cellElement.children().css({ 'color': '#000' });
        }
        if (e.rowType === 'groupFooter') {
            e.cellElement.css({ 'font-style': 'italic' });
        }
    }
</script>
